/*
 * SalesDAO.java
 *
 * Created on March 25, 2007, 1:34 PM
 */

package edu.byu.isys413.mystuff.sales;

import edu.byu.isys413.mystuff.*;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * This is a data access object for the sales revenue source. This is to be integrated with
 * the RevenueSourceFactory. This allows for a sales revenue source to be created and allows
 * a user to sell an object and this inherits methods from the revenue source DAO.
 */
public abstract class SalesDAO extends revaDAO{
    
    
    ///////////////////////////////////////
    ///   Singleton pattern
    
    private static SalesDAO instance = null;
    
    /** Creates a new instance of ConceptualDAO */
    private SalesDAO() {
    }
    
    public static synchronized SalesDAO getInstance() {
        if (instance == null) {
            instance = new SalesDAO();
        }
        return instance;
    }
    
    
    /////////////////////////////////
    ///   CREATE
    
    /**
     * There's no need for two creates because we don't need
     * a connection to create BOs.  We run the insert statement
     * later, when it get's saved for the first time.
     */
    public revaBO create() {
        SalesBO salesBO = null;
        try {
            String id = GUID.generate();
            
            //salesBO = new ConceptualBO(id);
            
            Cache c = Cache.getInstance();
            
           // c.put(salesBO.g(), salesBO);
        } catch (DataException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return salesBO;
    }
    
    
    /////////////////////////////////////
    ///   READ
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public revaBO read(String id) throws DataException {
        ConceptualBO conceptualBO = null;
        Connection conn = null;
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        if(c.get(id)!=null){
            conceptualBO = (ConceptualBO)(c.get(id));
        } else{
            try {
                conn = ConnectionPool.getInstance().get();
                conceptualBO = read(id, conn);
                ConnectionPool.getInstance().release(conn);
            }catch (Exception e) {
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
        }
        
        return revaBO;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     */
    synchronized ProductBO read(String id, Connection conn) throws SQLException, DataException {
        ConceptualBO conceptualBO = null;
        // check cache and return if found
        Cache c = Cache.getInstance();
        if(c.get(id)!=null){
            return (ConceptualBO)(c.get(id));
        } else{
            // if not in the cache, get a result set from
            // a SELECT * FROM table WHERE id=guid
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM Conceptual WHERE productId=?");
            //give the value of id
            pstmt.setString(1,id);
            ResultSet rs = pstmt.executeQuery();
            
            if(rs.next()){
                conceptualBO.setproductId(rs.getString("ProductId"));
                conceptualBO.setproductName(rs.getString("ProductName"));
                conceptualBO.setdescription(rs.getString("Description"));
                conceptualBO.setaverageCost(rs.getDouble("averageCost"));
                conceptualBO.setAlreadyInDB(true);
                
                //String membID = MembershipDAO.getInstance().getMembIDFromCustID(id);
                //MembershipBO memb = MembershipDAO.getInstance().read(membID);
                //cust.setMembership(memb);
                
                c.put(id,cust);
            } else{
                throw new DataException("Object was not found in the database.");
            }
        }
        return conceptualBO;
    }
    
    public List<String[]> getAll() throws DataException {
        
        
        List<String[]> list = new ArrayList<String[]>();
        
        Connection conn = null;
        
        try {
            // get the connection
            conn = ConnectionPool.getInstance().get();
            
            
            Statement stmt = conn.createStatement();
            
            ResultSet rs = stmt.executeQuery("SELECT * From Conceptual_Product");
            
            // while loop to populate the list from the results
            while(rs.next()){
                String[] temp = new String[2];
                temp[0] = rs.getString("productId");
                temp[1] = rs.getString("productName");
                temp[2] = rs.getString("description");
                temp[3] = rs.getString("averageCost");
                list.add(temp);
            }
            
        } catch (ConnectionPoolException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            throw new DataException("Conceptual Product information not retrieved");
        }
        try {
            // release my connection
            ConnectionPool.getInstance().release(conn);
        } catch (ConnectionPoolException ex) {
            ex.printStackTrace();
        }
        // return the list
        return list;
    }
    
    
    
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    public synchronized void save(ProductBO productBO) throws DataException {
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            save(productBO, conn);
            ConnectionPool.getInstance().release(conn);
        }catch (Exception ex) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throw new DataException("Could not save the record");
        }
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(ProductBO ProductBO, Connection conn) throws SQLException, DataException {
        try {
            // check the dirty flag in the object.  if it is dirty,
            // run update or insert
            if (productBo.isDirty()) {
                if (cust.isAlreadyInDB()) {
                    update(cust, conn);
                }else{
                    insert(productBO, conn);
                }
                // set the dirty flag to false now that we've saved it
                productBO.setDirty(false);
                Cache c = Cache.getInstance();
                c.touch(productBO.getId());
            }
        } catch (DataException ex) {
            throw ex;
        } catch (SQLException ex) {
            throw ex;
        }
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(ProductBO productBO, Connection conn) throws SQLException, DataException {
        // do the update statement
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Conceptual_Product SET productId=?,productName=?,=?,description=?,averageCost=? WHERE productId=?");
        try {
            
            pstmt.setString(1,productBO.getproductId());
            pstmt.setString(1,productBO.productName());
            pstmt.setString(2,productBO.descrtiption());
            pstmt.setString(3,productBO.averageCost());
            
            pstmt.executeUpdate();
            pstmt.close();
            conn.commit();
        } catch (SQLException ex) {
            throw ex;
        }
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(ProductBO productBO, Connection conn) throws SQLException, DataException {
        // do the insert SQL statement
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Conceptual_Product (,lname,address,city,state,zip,phone,email,custid) VALUES (?,?,?,?,?,?,?,?,?)");
        try {
            pstmt.setString(1,cust.getFname());
            pstmt.setString(2,cust.getLname());
            
            pstmt.executeUpdate();
            pstmt.close();
            conn.commit();
        } catch (SQLException ex) {
            throw ex;
        }
        // tell the object that it's now in the db (so we call update next time not insert)
        cust.setAlreadyInDB(true);
    }
    
    ////////////////////////////////////
    ///   DELETE
    
    // we are not supporting because deleting customer will
    //create referential error in the membership table and
    //member_area_interest table
}